<?php
App::import('Vendor', 'php-excel-reader/excel_reader2'); 
class ImportsController extends AppController {

	var $name = 'Imports';
    var $components = array('Uploader.Uploader');
    var $uses = array('Import','Sinhvien','Phanloaisv','Tinhtp','Quanhuyen','Phuongxa','Dantoc','Tongiao','Chidoansv','Khoa','Khoahoc','Canbo','Phanloaicb','Tgiachidoan','Tgiachidoancb','User');//'Chidoancb'
    function beforeFilter(){
        $this->Uploader->uploadDir = 'files/'; //thu muc chua file upload
        $this->Uploader->enableUpload = true;
        $this->Uploader->maxFileSize = '2M'; // quy dinh dung luong duoc upload len toi da la 2 Megabytes
        $this->Uploader->maxNameLength = 25;//do dai cua ten file
        $this->Uploader->tempDir = TMP;
    }
    
    
    
    /**
     * function createcol($temp): du lieu dua vao la mang cac gia tri dc doc tu file excel
     * Tao cot trong bang tam: truoc tien la xoa du lieu trong bang neu co, 
     * sau do drop cac cot du lieu roi moi add du cac cot vo
     * Tao cac cot du lieu la so thu tu cua cac cot trong file
     **/
    function createcol($temp){
        $this->Import->query("TRUNCATE TABLE imports");
        //drop cot
        $columns=$this->Import->query('Show columns from imports');
        $oldcols=count($columns);
        $oldcols=$oldcols-1;
        $newcols=count($temp[1]);
        if($oldcols<$newcols){
            for($j=$oldcols+1;$j<=$newcols;$j++)
            {
                $this->Import->query("ALTER TABLE imports ADD `".$j."` varchar (255)");
            }
        }elseif($oldcols>$newcols){
            for($j=$newcols+1;$j<=$oldcols;$j++)
            {
                $this->Import->query("ALTER TABLE imports DROP `".$j."`");
            }
        }
        /*
        $i=1;
        foreach($columns as $column){
            if($i>1){//loai id ra
                $this->Import->query("ALTER TABLE imports DROP `".$column['COLUMNS']['Field']."`");
            }
            $i++;
        }
        //add cot
        for($j=1;$j<=$newcols;$j++)
        {
            $this->Import->query("ALTER TABLE imports ADD `".$j."` varchar (255)");
        }
        */
    }
    
    /**
     * function savetable($temp): du lieu dua vao la mang cac gia tri dc doc tu file excel
     * Chay vong lap de gan gia tri vao chuoi query 
     * Sau do save du lieu vao bang tam
     */
    function savetable($temp){
        //lay gia tri ra tu mang
        $i=1;
        foreach($temp as $value){
            //pr($value);            
            if($i>1){
                $this->Import->create();
                //vong lap chay lan dau se tao chuoi query như sau
                $query = "'',";
                //tieps tuc gan cac gia tri vao chuoi query
                for($x=1;$x<count($temp[1]);$x++)
                {   
                    
                    if(!mb_check_encoding($value[$x], 'UTF-8')) $value[$x]=utf8_encode($value[$x]);
                    //echo $value[$x];
                    $query= $query."'".$value[$x]."',"; 
                    
                }  
                
                //cuoi chuoi
                $x=count($temp[1]);
                $query=$query."'".$value[$x]."'";
                //insert vao bang imports
                $this->Import->query("Insert into imports values($query)");
            }
            $i++;    
        }//exit;
    }
    
    /**
     * function upload()
     * Upload file excel vao webroot/files
     * Doc du lieu tu file roi dua du lieu ra view dong thoi read dua vao mang
     * tao cac cot trong bang tam va save du lieu vao bang tam
     * dung session de luu thong tin cua file vua moi up len
     * */
    function upload() {
        if (!empty($this->data)) {
             if ($file = $this->Uploader->upload('fileName')) {
             	$this->Session->setFlash(__('Upload thành công',true),'success');
                $data = new Spreadsheet_Excel_Reader('files/'.$file['name'], true);
                //$data = new Spreadsheet_Excel_Reader('cbdscb.xls', true);
                //$data = new Spreadsheet_Excel_Reader('cbplPLDV.xls', true);
                //$this->set('data', $data);
                $temp = $data->dumptoarray();
                $this->Session->write('imports',$temp);
                //pr($temp);exit;
                //echo 'after';
                //pr(htmlentities($temp[1][2]));exit;
                $this->createcol($temp);
                $this->savetable($temp);
                $this->Session->write('Uploadfile',$file);
                //$this->redirect(array('action'=>'review'));
                //$this->redirect(array('action'=>'readtemptable'));
                $this->redirect(array('controller'=>'imports','action'=>'index'));
             }else{
                $this->Session->setFlash(__('Có lỗi xảy ra do định dạng file không đúng(chỉ chấp nhận định dạng .xls) .Vui lòng thử lại',true),'error');
             }//	
        }
    }
    
    
    /**
	 * function review()
     * show bang sau khi upload dong
     * tao cac cot tuong ung va save du lieu từ file vào
	 */
	function review() {
        if($this->Session->check('Uploadfile')){
            $fileupload=$this->Session->read('Uploadfile');
            $prefix=substr($fileupload['name'],0,2);   
            $data = new Spreadsheet_Excel_Reader('files/'.$fileupload['name'], true);
            //$temp = $data->dumptoarray();
            //pr($temp);
            //$this->createcol($temp);
            //$this->savetable($temp);
            $this->set('data', $data);
        }else echo "Not found";
	}
    function readtemptable(){
        $fileupload=$this->Session->read('Uploadfile');   
        $data = new Spreadsheet_Excel_Reader('files/'.$fileupload['name'], true);
        //$data = new Spreadsheet_Excel_Reader('cbplPLDV.xls', true);
        $data=$data->dumptoarray();
        $head=$data[1];
        $this->set('head',$head);
        $content=$this->Import->find('all');
        $columns=$this->Import->query('Show columns from imports');
        $columns=count($columns);
        $this->set(compact('content','columns'));
    }
    
    function index() {
        $fileupload=$this->Session->read('Uploadfile');   
        //$data = new Spreadsheet_Excel_Reader('files/'.$fileupload['name'], true);
        //$data = new Spreadsheet_Excel_Reader('cbplPLDV.xls', true);
        //$data=$data->dumptoarray();
        $data=$this->Session->read('imports');
        $head=$data[1];
        $this->set('head',$head);
        $columns=$this->Import->query('Show columns from imports');
        $columns=count($columns);
        $this->set('columns',$columns);
		$this->Import->recursive = 0;
        $imports=$this->Import->query('select * from imports');
        //$imports=$this->Import->find('all');
		$this->set('imports', $imports);
	}
    
    /**
     * function existed($notice, $object, $data)
     * tra ve cau thong bao DA TON TAI mot $object co gia tri la $data trong he thong
     * */     
    function existed($notice=null,$object=null,$data=null){
        $notice=$notice.$object.': '.$data.' đã tồn tại trong hệ thống! </br>';
        return $notice;
    }
    
    /**
     * function notfound($notice, $object, $data)
     * tra ve cau thong bao KHONG TIM THAY mot $object co gia tri la $data trong he thong
     * */
    function notfound($notice=null, $object=null, $data=null){
        $notice=$notice.'Không tìm thấy '.$object.' '.$data.' trong hệ thống! </br>';
        return $notice;
    }
    
    /**
     * function import()
     * lay du lieu da save vao bang imports ra
     * kiem tra prefix cua file de xac dinh cac khoa ngoai can do`
     * dau tien la check 2 ki tu dau tien cua file la sv hay cb, sau do chech 2 ki tu tiep theo la action gi
     * tim cac khoa ngoai va kiem tra du lieu cua khoa ngoai
     * kiem tra du lieu cua file da ton tai trong csdl chua
     * luu du lieu tu bang imports vao bang dich
     * */
    //if(!mb_check_encoding($record [$i] [4], 'UTF-8')) $record [$i] [4]=utf8_encode($record [$i] [4]);

    function import(){
        $importdata=$this->Import->find('all');
        //pr($importdata);
        $quantriht=0;//3 - quan tri he thong
        $quanlyttcd=0;//2 - quan ly thong tin chi doan
        $congtacdoancs=0;//1 - cong tac doan co so
        $accessdenied="Bạn không có quyền import file này";
        $user=$this->Auth->user();
        $phanquyen=$this->Phanquyen->find('all',array('conditions'=>array('user_id'=>$user['User']['id']),
                                                        'fields'=>'role_id'));
        foreach($phanquyen as $quyen){
            if($quyen['Phanquyen']['role_id']==3){//quyen quan tri he thong
                $quantriht=1;
            }elseif($quyen['Phanquyen']['role_id']==2){
                    $quanlyttcd=1;
                }elseif($quyen['Phanquyen']['role_id']==1){
                    $congtacdoancs=1;
                }
        }
        if($this->Session->check('Uploadfile')){
            $fileupload=$this->Session->read('Uploadfile');
            //pr($fileupload);  
            $prefix=substr($fileupload['name'],0,2);   
            switch($prefix){
                case 'sv':
                $action=substr($fileupload['name'],2,2);
                if($action=='ds'){
                    /** imp sv */
                    if($quanlyttcd==1){
                            foreach($importdata as $data){ 
                            //tim khoa ngoai phuong xa, ton giao, dan toc, chi doan
                            $this->Phuongxa->recursive=-1;
                            $px=$this->Phuongxa->findByTenphuongxa(trim($data['Import']['14']));
                            $this->Tongiao->recursive=-1;
                            $tg=$this->Tongiao->findByTentongiao(trim($data['Import']['8']));
                            $this->Dantoc->recursive=-1;
                            $dt=$this->Dantoc->findByTendantoc(trim($data['Import']['7']));
                            $this->Chidoansv->recursive=-1;
                            $cd=$this->Chidoansv->findByMachidoansv(trim($data['Import']['2']));
                            //check khoa ngoai co ton tai khong
                            if($px!=null){
                                if($tg!=null){
                                    if($dt!=null){
                                        if($cd!=null){
                                            //neu khoa ngoai co ton tai roi thi check xem du lieu dinh import da ton tai trong csdl chua
                                            $this->Sinhvien->recursive=-1;
                                            $sv=$this->Sinhvien->findByMssv(trim($data['Import']['4']));
                                            //reset cai arrray lai = null
                                            $sinhvien=null;
    										//echo date('d/m/Y', strtotime($data['Import']['12']));
                                            $sinhvien[]=array(  
                                                                'mssv'=>$data['Import']['4'],
                                                                'phuongxa_id'=>$px['Phuongxa']['id'],
                                                                'tongiao_id'=>$tg['Tongiao']['id'], 
                                                                'dantoc_id'=>$dt['Dantoc']['id'],  
                                                                'hoten'=>$data['Import']['5'], 
                                                                'ngaysinh'=>date('Y-m-d', strtotime($data['Import']['6'])), 
                                                                'noisinh'=>$data['Import']['13'], 
                                                                'diachi'=>$data['Import']['18'], 
                                                                'phai'=>$data['Import']['10'], //10
                                                                'sodienthoai'=>$data['Import']['19'], 
                                                                'email'=>$data['Import']['20'], 
                                                                'ngayvaodoan'=>date('Y-m-d', strtotime($data['Import']['12'])), 
                                                                'noivaodoan'=>$data['Import']['9'], 
                                                                //'ngayvaodang'=>'', 
                                                                //'noivaodang'=>'', 
                                                                //'ngaychuyenden'=>'', 
                                                                //'ngaychuyendi'=>'', 
                                                                //'truongthanhdoan'=>'', 
                                                                'ghichu'=>$data['Import']['21']
                                                                );
                                             //create user
                                             //$user=null;
                                             //$password=trim($data['Import']['4']);
                                             //$user[]=array('username'=>trim($data['Import']['4']),'password'=>Security::hash($password, 'md5', true));
                                            if($sv==null){
                                                //neu chua thi save vo
                                                //pr($sinhvien);
                                                $this->Sinhvien->create();
                                                $this->Sinhvien->saveAll($sinhvien);
                                                $this->Sinhvien->id = false;    
                                                $lastid=$this->Sinhvien->getLastInsertID();
                                                //pr($lastid);
                                                $this->Tgiachidoan->create();
                                                $this->Tgiachidoan->saveAll(array('chidoansv_id'=>$cd['Chidoansv']['id'],'sinhvien_id'=>$lastid));
                                                $this->Tgiachidoan->id=false;
                                                //save user
                                                //$this->User->create();
                                                //$this->User->saveAll($user);
                                                //$this->User->id = false;  
                                            }else {
                                                $mssv=$this->existed($mssv,'MSSV',$sv['Sinhvien']['mssv']);
                                                $this->set('mssv',$mssv);
                                            }//echo 'MSSV: '.$sv['Sinhvien']['id'].' đã tồn tại trong hệ thống! </br>'; 
                                        }else {
                                            $chidoan=$this->notfound($chidoan,'MSCD',$data['Import']['2']);
                                            $this->set('chidoan',$chidoan);
                                        }//echo 'Chi Ðoàn có mã số: '.$data['Import']['2'].' không tìm thấy! </br>';
                                    }else {
                                        $dantoc=$this->notfound($dantoc,'Dan toc',$data['Import']['7']);
                                        $this->set('dantoc',$dantoc);
                                    }//echo 'Dân tộc '.$data['Import']['7'].' không tìm thấy! </br>';
                                }else {
                                    $tongiao=$this->notfound($tongiao,'Ton giao',$data['Import']['8']);
                                    $this->set('tongiao',$tongiao);
                                }//echo 'Tôn giáo '.$data['Import']['8'].' không tìm thấy! </br>';
                            }else{
                                $phuongxa=$this->notfound($phuongxa,'Phuong xa',$data['Import']['14']);
                                $this->set('phuongxa',$phuongxa);
                            } //echo 'Phường xã '.$data['Import']['14'].' không tìm thấy! </br>';
                        }
                        if(!isset($mssv)&&!isset($chidoan)&&!isset($dantoc)&&!isset($tongiao)&&!isset($phuongxa)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'chidoansvs','action'=>'caychidoan'));    
                        }
                    }else $this->set('accessdenied',$accessdenied);//check quyen
                    
                }elseif($action=='pl'){
                    /** imp danh sach phan loai doan vien sv 
                    *   phai check xem ma lop cua bi thu co giong voi ma lop cua may ban trong ds import de phan loai hay khong
                    *   tranh truong hop bi thu phan loai ds cac ban ngoai lop
                    */
                    if($congtacdoancs==1){
                        $this->loadModel('Thoigianchophep');
                        $thoigian=$this->Thoigianchophep->find('first',array('conditions'=>array('id'=>1)));
                        //$this->set('thoigian',$thoigian);
                        if(date('Y-m-d')>=$thoigian['Thoigianchophep']['moc1']&&date('Y-m-d')<=$thoigian['Thoigianchophep']['moc2']){
                            $hocky=$this->getHocKyHt();
                            $namhocht_id=$this->Session->read('namhocht_id');
                            foreach($importdata as $data){ 
                                /*PLSV theo hoc ky  */                    
                                $this->Sinhvien->recursive=-1;
                                $sv=$this->Sinhvien->findByMssv(trim($data['Import']['2']));
                                $error = 1;
                                if($sv!=null){
                                    $this->Tgiachidoan->recursive=-1;
                                    $cdht_id=$this->Tgiachidoan->find('first',array('conditions'=>array('sinhvien_id'=>$sv['Sinhvien']['id'])));
                                    if($cdht_id['Tgiachidoan']['chidoansv_id']==$this->Session->read('chidoanht_id')){
                                        $this->Phanloaisv->recursive=-1;
                                        $svid=$this->Phanloaisv->find('first',array('conditions'=>array('sinhvien_id'=>$sv['Sinhvien']['id'],'hocky_id'=>$hocky['Hocky']['id'])));//xem xem da imp chua
                                        if($svid==null){
                                            $this->Phanloaisv->create();
                                            $this->Phanloaisv->saveAll(array('xeploai_id'=>'5','hocky_id'=>$hocky['Hocky']['id'],'sinhvien_id'=>$sv['Sinhvien']['id'],'diemrenluyen'=>$data['Import']['5'],'diemtbhk'=>$data['Import']['4'],'ghichu'=>null,'duyet'=>0,'chinhsua'=>0));
                                            $this->Phanloaisv->id = false;  
                                        }else {
                                            $mssv=$this->existed($mssv,'MSSV',$sv['Sinhvien']['mssv']);
                                            $this->set('mssv',$mssv);
                                            $error = 0;
                                        }//echo 'MSSV: '.$svid['Phanloaisv']['sinhvien_id'].' dã có trong danh sách! </br>';
                                    }else {
                                        $cdht= $cdht.'MSSV: '.$sv['Sinhvien']['mssv'].' không thuộc chi đoàn của bạn. </br>';
                                        $this->set('cdht',$cdht);
                                        $error = 0;//loi, hong redirect
                                    }
                                 }else{
                                    $mssvn=$this->notfound($mssvn,'MSSV',$data['Import']['2']);
                                    $this->set('mssvn',$mssvn);
                                    $error = 0;
                                 } //echo 'Không tìm th?y MSSV: '.$data['Import']['2'].' trong danh sách sinh viên! </br>';
                                 /*end*/
                                 /*PLSV theo nam hoc
                                 $this->Sinhvien->recursive=-1;
                                 $this->loadModel('Phanloaisinhvien');
                                $sv=$this->Sinhvien->findByMssv(trim($data['Import']['2']));
                                $error = 1;
                                if($sv!=null){
                                    $this->Tgiachidoan->recursive=-1;
                                    $cdht_id=$this->Tgiachidoan->find('first',array('conditions'=>array('sinhvien_id'=>$sv['Sinhvien']['id'])));
                                    if($cdht_id['Tgiachidoan']['chidoansv_id']==$this->Session->read('chidoanht_id')){
                                        $this->Phanloaisinhvien->recursive=-1;
                                        $svid=$this->Phanloaisinhvien->find('first',array('conditions'=>array('sinhvien_id'=>$sv['Sinhvien']['id'],'namhoc_id'=>$namhocht_id)));//xem xem da imp chua
                                        if($svid==null){
                                            $this->Phanloaisinhvien->create();
                                            $this->Phanloaisinhvien->saveAll(array('xeploai_id'=>'5','namhoc_id'=>$namhocht_id,'sinhvien_id'=>$sv['Sinhvien']['id'],'diemrenluyen'=>$data['Import']['5'],'diemtbhk'=>$data['Import']['4'],'ghichu'=>null,'duyet'=>0,'chinhsua'=>0));
                                            $this->Phanloaisinhvien->id = false;  
                                        }else {
                                            $mssv=$this->existed($mssv,'MSSV',$sv['Sinhvien']['mssv']);
                                            $this->set('mssv',$mssv);
                                            $error = 0;
                                        }//echo 'MSSV: '.$svid['Phanloaisv']['sinhvien_id'].' dã có trong danh sách! </br>';
                                    }else {
                                        $cdht= $cdht.'MSSV: '.$sv['Sinhvien']['mssv'].' không thuộc chi đoàn của bạn. <br/>';
                                        $this->set('cdht',$cdht);
                                        $error = 0;//loi, hong redirect
                                    }
                                 }else{
                                    $mssvn=$this->notfound($mssvn,'MSSV',$data['Import']['2']);
                                    $this->set('mssvn',$mssvn);
                                    $error = 0;
                                 }   */
                                 /*end*/   
                               }
                           if(!isset($mssvn)){
                                if($error==1){
                                    $this->Session->setFlash(__('Import thành công!',true),'success');
                                    $this->redirect(array('controller'=>'phanloaisvs','action'=>'phanloai'));
                                    //$this->redirect(array('controller'=>'phanloaisinhviens','action'=>'phanloai'));
                                    //$this->redirect(array('controller'=>'phanloaisinhviens','action'=>'index'));
                                }
                           }
                      }else{
                            $hethan="Đã hết hạn phân loại sinh viên, bạn không có quyền import trong thời gian này!";
                            $this->set('hethan',$hethan);
                      }//check thoi gian import 
                    }else $this->set('accessdenied',$accessdenied);//check quyen
                    
                }elseif($action=='cd'){
                    /** imp chidoansv */
                    if($quanlyttcd==1){
                            foreach($importdata as $data){ 
                            $this->Khoa->recursive=-1;
                            //$khoa=$this->Khoa->findByTenkhoa(trim($data['Import']['3']));
                            $khoa=$this->Khoa->findByMakhoa(trim($data['Import']['3']));
                            $this->Khoahoc->recursive=-1;
                            $khoahoc=$this->Khoahoc->findByNambatdau(trim($data['Import']['6']));
                            $this->loadModel('Nganh');
                            $nganh=$this->Nganh->findByTennganh(trim($data['Import']['2']));
                            if($khoa!=null){
                                if($khoahoc!=null){
                                    if($nganh!=null){
                                        $this->Chidoansv->recursive=-1;
                                        $cd=$this->Chidoansv->findByMachidoansv(trim($data['Import']['4']));
                                        //pr($nganh);exit;
                                        //pr($cd);exit;
                                        if($cd==null){
                                            $this->Chidoansv->create();
                                            $this->Chidoansv->saveAll(array(
                                                                        'machidoansv'=>$data['Import']['4'],
                                                                        'khoa_id'=>$khoa['Khoa']['id'],
                                                                        'khoahoc_id'=>$khoahoc['Khoahoc']['id'],
                                                                        'tenchidoan'=>$data['Import']['5'],
                                                                        'ghichu'=>$data['Import']['7'],
                                                                        'nganh_id'=>$nganh['Nganh']['id']));
                                            $this->Chidoansv->id = false;    
                                        }else {
                                            $chidoan=$this->existed($chidoan,'MSCD',$data['Import']['4']);
                                            $this->set('chidoan',$chidoan);
                                        }//echo 'Chi doàn có mã s? '.$cd['Chidoansv']['id'].' dã t?n t?i trong h? th?ng! </br>';
                                    }else{
                                         $nganhhoc=$this->notfound($nganhhoc,'Nganh hoc',$data['Import']['2']);
                                         $this->set('nganhhoc',$nganhhoc);
                                    }
                                    
                                }else {
                                   $khoahoc=$this->notfound($khoahoc,'Khoa hoc',$data['Import']['6']);
                                   $this->set('khoahoc',$khoahoc); 
                                }//echo 'Khóa h?c có nam vào là '.$data['Import']['6'].' không t?n t?i! </br>';
                            }else {
                                $khoa=$this->notfound($khoa,'Khoa',$data['Import']['3']);
                                $this->set('khoa',$khoa); 
                            }//echo 'Khóa '.$data['Import']['3'].' không tìm thấy! </br>';
                        }
                        if(isset($khoa)||isset($khoahoc)||isset($nganhhoc)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'chidoansvs','action'=>'index',$khoa['Khoa']['id']));    
                        }     
                    }else $this->set('accessdenied',$accessdenied);
                    
                }else $this->set('actionnotfound','Prefix của bạn không đúng!');//echo "khong co action?";
                break;
                case 'cb':
                    $action=substr($fileupload['name'],2,2);
                    if($action=='ds'){
                        if($quanlyttcd==1){
                                foreach($importdata as $data){ 
                                //tim khoa ngoai phuong xa, ton giao, dan toc, chi doań
                                $this->Phuongxa->recursive=-1;
                                $px=$this->Phuongxa->findByTenphuongxa(trim($data['Import']['14']));
                                $this->Tongiao->recursive=-1;
                                $tg=$this->Tongiao->findByTentongiao(trim($data['Import']['8']));
                                $this->Dantoc->recursive=-1;
                                $dt=$this->Dantoc->findByTendantoc(trim($data['Import']['7']));
                                $this->Chidoansv->recursive=-1;
                                $cd=$this->Chidoansv->findByMachidoansv(trim($data['Import']['2']));
                                //check khoa ngoai co ton tai khong
                                if($px!=null){
                                    if($tg!=null){
                                        if($dt!=null){
                                            if($cd!=null){
                                                //neu khoa ngoai co ton tai roi thi check xem du lieu dinh import da ton tai trong csdl chua
                                                $this->Canbo->recursive=-1;
                                                $cb=$this->Canbo->findByMscb($data['Import']['4']);
                                                //echo date('d-m-y',strtotime($data['Import']['12']));
                                                $canbo=null;
                                                $canbo[]=array(  'mscb'=>$data['Import']['4'],
                                                                    'phuongxa_id'=>$px['Phuongxa']['id'],
                                                                    'tongiao_id'=>$tg['Tongiao']['id'], 
                                                                    'dantoc_id'=>$dt['Dantoc']['id'], 
                                                                    //'chidoancb_id'=>$cd['Chidoancb']['id'], 
                                                                    'hoten'=>$data['Import']['5'], 
                                                                    'ngaysinh'=>date('Y-m-d', strtotime($data['Import']['6'])), 
                                                                    'noisinh'=>$data['Import']['13'], 
                                                                    'diachi'=>$data['Import']['18'], 
                                                                    'phai'=>$data['Import']['10'], //10
                                                                    'sodienthoai'=>$data['Import']['19'], //cai nay ko thay trong table 
                                                                    'email'=>$data['Import']['20'], 
                                                                    'ngayvaodoan'=>date('Y-m-d', strtotime($data['Import']['12'])), 
                                                                    'noivaodoan'=>$data['Import']['9'], 
                                                                    //'ngayvaodang'=>'', 
                                                                    //'noivaodang'=>'', 
                                                                    //'ngaychuyenden'=>'', 
                                                                    //'ngaychuyendi'=>'', 
                                                                    //'ngayttdoan'=>'', 
                                                                    'ghichu'=>$data['Import']['21']
                                                                    );
                                                //create user
                                                //$usercb=null;
                                                //$usercb[]=array('username'=>$data['Import']['4'],'password'=>$data['Import']['4']);                                                             
                                                if($cb==null){
                                                    //neu chua thi save vo
                                                    $this->Canbo->create();
                                                    $this->Canbo->saveAll($canbo);
                                                    $this->Canbo->id = false; 
                                                    $lastid=$this->Canbo->getLastInsertID();
                                                    //pr($lastid);
                                                    $this->Tgiachidoancb->create();
                                                    $this->Tgiachidoancb->saveAll(array('chidoansv_id'=>$cd['Chidoansv']['id'],'canbo_id'=>$lastid));
                                                    $this->Tgiachidoancb->id=false;  
                                                    //save user
                                                    //$this->User->create();
                                                    //$this->User->saveAll($usercb);
                                                    //$this->User->id = false;     
                                                }else {
                                                    $mscb=$this->existed($mscb,'MSCB',$cb['Canbo']['mscb']);  
                                                    $this->set('mscb',$mscb);  
                                                }//echo 'MSCB: '.$cb['Canbo']['id'].' đã tồn tại trong hệ thống! </br>';
                                            }else {
                                                $chidoan=$this->notfound($chidoan,'MSCD',$data['Import']['2']);  
                                                $this->set('chidoan',$chidoan);  
                                            }//echo 'Chi Đoàn có mã số: '.$data['Import']['2'].' không tìm thấy! </br>';
                                        }else {
                                            $dantoc=$this->notfound($dantoc,'Dan toc',$data['Import']['7']);  
                                            $this->set('dantoc',$dantoc);  
                                        }//echo 'Dân tộc '.$data['Import']['7'].' không tìm thấy! </br>';
                                    }else {
                                        $tongiao=$this->notfound($tongiao,'Ton giao',$data['Import']['8']);  
                                        $this->set('tongiao',$tongiao);
                                    }//echo 'Tôn giáo '.$data['Import']['8'].' không tìm thấy! </br>';
                                }else {
                                    $phuongxa=$this->notfound($phuongxa,'Phuong xa',$data['Import']['14']);  
                                    $this->set('phuongxa',$phuongxa);
                                }//echo 'Phường xã '.$data['Import']['14'].' không tìm thấy! </br>';
                            }
                            if(!isset($mscb)&&!isset($chidoan)&&!isset($tongiao)&&!isset($dantoc)&&!isset($phuongxa)){
                                $this->Session->setFlash(__('Import thành công!',true),'success');
                                $this->redirect(array('controller'=>'chidoansvs','action'=>'caychidoan'));    
                            } 
                        }else $this->set('accessdenied',$accessdenied);//quyen
                    }elseif($action=='pl'){
                        if($congtacdoancs==1){
                            foreach($importdata as $data){ 
                            $this->Canbo->recursive=-1;
                            $cb=$this->Canbo->findByMscb($data['Import']['3']);
                            if($cb!=null){
                                $namhocht_id=$this->Session->read('namhocht_id');
                                $this->Tgiachidoancb->recursive=-1;
                                $cdht_id=$this->Tgiachidoancb->find('first',array('conditions'=>array('canbo_id'=>$cb['Canbo']['id'])));
                                if($cdht_id['Tgiachidoancb']['chidoansv_id']==$this->Session->read('chidoanht_id')){
                                    $this->Phanloaicb->recursive=-1;
                                    $cbid=$this->Phanloaicb->find('first',array('conditions'=>array('canbo_id'=>$cb['Canbo']['id'],'namhoc_id'=>$namhocht_id)));
                                    if($cbid==null){
                                        $this->Phanloaicb->create();
                                        $this->Phanloaicb->saveAll(array('xeploai_id'=>'5','canbo_id'=>$cb['Canbo']['id'],'namhoc_id'=>$namhocht_id,'danhhieu'=>'','tenphongtrao'=>'','ghichu'=>$data['Import']['9']));
                                        $this->Phanloaicb->id = false;    
                                    }else {
                                        $mscb=$this->existed($mscb,'MSCB',$cbid['Phanloaicb']['canbo_id']);  
                                        $this->set('mscb',$mscb);   
                                    }
                                }else {
                                    $cdht= $cdht.'MSCB: '.$cb['Canbo']['mscb'].' không thuộc chi đoàn của bạn. </br>';
                                    $this->set('cdht',$cdht);
                                }
                             }else{
                                $mscbn=$this->notfound($mscbn,'MSCB',$data['Import']['3']);  
                                $this->set('mscbn',$mscbn); 
                             } //echo 'Không tìm thấy MSCB: '.$data['Import']['3'].' trong danh sách cán bộ! </br>';   
                            } 
                            if(!isset($mscbn)&&!isset($mscb)&&!isset($cdht)){
                                $this->Session->setFlash(__('Import thành công!',true),'success');
                                $this->redirect(array('controller'=>'phanloaicbs','action'=>'index'));    
                            } 
                        }else $this->set('accessdenied',$accessdenied);
                        
                    }else $this->set('$actionnotfound','Prefix của bạn không đúng!');//echo "khong co action?";
                break;  
                case 'tp':
                    /** imp tinhtp */
                    if($quantriht==1){
                        foreach($importdata as $data){ 
                            $tp=$this->Tinhtp->findByTentinhtp(trim($data['Import']['2']));
                            if($tp==null){
                                $this->Tinhtp->create();
                                $this->Tinhtp->saveAll(array('tentinhtp'=>$data['Import']['2'],'ghichu'=>$data['Import']['3']));
                                $this->Tinhtp->id = false;   
                            }else {
                                $tinhtp=$this->existed($tinhtp,'Tinh/TP',$data['Import']['2']);
                                $this->set('tinhtp',$tinhtp);
                            }//echo 'T?nh/thành ph? '.$data['Import']['2'].' dã t?n t?i trong h? th?ng!</br>';
                        }
                        if(!isset($tinhtp)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'tinhtps','action'=>'index'));    
                        } 
                    }else $this->set('accessdenied',$accessdenied);
                break;
                case 'qh':
                    /** imp quan huyen */
                    if($quantriht==1){
                        foreach($importdata as $data){ 
                            $this->Tinhtp->recursive=-1;
                            $tp=$this->Tinhtp->findByTentinhtp(trim($data['Import']['3']));
                            if($tp!=null){
                                $this->Quanhuyen->recursive=-1;
                                $qh=$this->Quanhuyen->findByTenquanhuyen(trim($data['Import']['2']));
                                if($qh==null){
                                    $this->Quanhuyen->create();
                                    $this->Quanhuyen->saveAll(array('tenquanhuyen'=>$data['Import']['2'],'tinhtp_id'=>$tp['Tinhtp']['id'],'ghichu'=>$data['Import']['4']));
                                    $this->Quanhuyen->id = false;    
                                }else {
                                    $quanhuyen=$this->existed($quanhuyen,'Quan/huyen',$qh['Quanhuyen']['tenquanhuyen']);
                                    $this->set('quanhuyen',$quanhuyen);
                                }//echo 'Qu?n/huy?n '.$qh['Quanhuyen']['quanhuyen'].' dã t?n t?i trong h? th?ng! </br>';    
                            }else {
                                $tinhtp=$this->notfound($tinhtp,'Tinh/TP',$data['Import']['2']);
                                $this->set('tinhtp',$tinhtp);
                            }//echo 'Không tìm th?y t?nh/thành ph? '.$data['Import']['2'].' trong danh sách!</br>';
                            
                        }
                        if(!isset($tinhtp)&&!isset($quanhuyen)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'quanhuyens','action'=>'index'));    
                        } 
                   }else $this->set('accessdenied',$accessdenied);
                break;
                case 'px':
                    // imp phuong xa
                    if($quantriht==1){
                        foreach($importdata as $data){ 
                            $this->Quanhuyen->recursive=-1;//=0 neu lay them tinhtp
                            $qh=$this->Quanhuyen->findByTenquanhuyen(trim($data['Import']['3']));
                            if($qh!=null){
                                $this->Phuongxa->recursive=-1;
                                $px=$this->Phuongxa->findByTenphuongxa(trim($data['Import']['2']));
                                if($px==null){
                                    $this->Phuongxa->create();
                                    $this->Phuongxa->saveAll(array('tenphuongxa'=>$data['Import']['2'],'quanhuyen_id'=>$qh['Quanhuyen']['id'],'ghichu'=>$data['Import']['5']));
                                    $this->Phuongxa->id = false;    
                                }else {
                                    $phuongxa=$this->existed($phuongxa,'Phuong xa',$px['Phuongxa']['tenphuongxa']);
                                    $this->set('phuongxa',$phuongxa);
                                }//echo 'Phu?ng/xã '.$px['Phuongxa']['phuongxa'].' dã t?n t?i trong h? th?ng! </br>';   
                            }else {
                                $quanhuyen=$this->notfound($quanhuyen,'Quan/huyen',$data['Import']['3']);
                                $this->set('quanhuyen',$quanhuyen);
                            }//echo 'Không tìm th?y qu?n/huy?n '.$data['Import']['3'].' trong danh sách!';
                        }
                        if(!isset($phuongxa)&&isset($quanhuyen)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'phuongxas','action'=>'index'));    
                        } 
                    }else $this->set('accessdenied',$accessdenied);
                break;
                case 'dt':
                    /** imp dantoc */
                    if($quantriht==1){
                        foreach($importdata as $data){ 
                            $this->Dantoc->recursive=-1;
                            $dt=$this->Dantoc->findByTendantoc(trim($data['Import']['2']));
                            if($dt==null){
                                $this->Dantoc->create();
                                $this->Dantoc->saveAll(array('tendantoc'=>$data['Import']['2'],'ghichu'=>$data['Import']['3']));
                                $this->Dantoc->id = false;    
                            }else{
                                $dantoc=$this->existed($dantoc,'Dan toc',$dt['Dantoc']['tendantoc']);
                                $this->set('dantoc',$dantoc);
                            } //echo 'Dân t?c '.$dt['Dantoc']['tendantoc'].' dã t?n t?i trong h? th?ng! <br />';
                        }
                        if(!isset($dantoc)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'dantocs','action'=>'index'));    
                        }    
                    }else $this->set('accessdenied',$accessdenied);
                    
                break;
                case 'tg':
                    /** imp ton giao */
                    if($quantriht==1){
                        foreach($importdata as $data){ 
                            $this->Tongiao->recursive=-1;
                            $tg=$this->Tongiao->findByTentongiao(trim($data['Import']['2']));
                            if($tg==null){
                                $this->Tongiao->create();
                                $this->Tongiao->saveAll(array('tentongiao'=>$data['Import']['2'],'ghichu'=>$data['Import']['3']));
                                $this->Tongiao->id = false;    
                            }else{
                                $tongiao=$this->existed($tongiao,'Ton giao',$tg['Tongiao']['tentongiao']);
                                $this->set('tongiao',$tongiao);
                            } //echo 'Tôn giáo '.$tg['Tongiao']['tentongiao'].' dã t?n t?i trong h? th?ng! </br>';
                        }
                        if(!isset($tongiao)){
                            $this->Session->setFlash(__('Import thành công!',true),'success');
                            $this->redirect(array('controller'=>'tongiaos','action'=>'index'));    
                        }    
                    }else $this->set('accessdenied',$accessdenied);
                break;
                default:
                    $this->set('prefixnotfound','Bạn chưa đặt prefix cho file '.$fileupload['name']) ;
            }
        }else $this->set('filenotfound','Không tìm thấy file đã upload');        
    }
    

	

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid import', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('import', $this->Import->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Import->create();
			if ($this->Import->save($this->data)) {
				$this->Session->setFlash(__('The import has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The import could not be saved. Please, try again.', true));
			}
		}
	}

	function edit($id = null) {
		$columns=$this->Import->query("show columns from imports");
		$this->set('columns',count($columns));
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid import', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Import->save($this->data)) {
				$this->Session->setFlash(__('The import has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The import could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Import->read(null, $id);
		}
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for import', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Import->delete($id)) {
			$this->Session->setFlash(__('Import deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Import was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}
}


?>